package com.ruoyi.rsikManage.Access;

import org.json.JSONArray;
import org.json.JSONObject;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
public class AccessDatabaseTest {

    private static final String dbPath = "\\\\192.168.4.85\\AudioClientLite(XuZhou)\\gb.mdb"; // 使用双反斜杠
    private static final String url = "jdbc:ucanaccess://" + dbPath.replace("\\", "/");
    private static final String tableName = "tlist"; // 指定要查询的表名
//
    /**
     * 获取 tlist 表的所有数据，并返回 JSONArray
     */
////
    public JSONArray getTlistData() {
        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {

            // 获取列信息
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            JSONArray rowsJson = new JSONArray();

            // 遍历结果集并构建JSON对象
            while (rs.next()) {
                JSONObject rowJson = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object value = rs.getObject(i);

                    // 如果是字符串类型，则去除前后空格
                    if (value instanceof String) {
                        value = ((String) value).trim();
                    }

                    rowJson.put(columnName, value);
                }
                rowsJson.put(rowJson);
            }

            return rowsJson;

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to retrieve data from table " + tableName, e);
        }
    }

    /**
     * 获取 tlist 表的所有数据，并将每条记录转换为去掉多余空格的 Map
     */
    public List<Map<String, Object>> getTlistDataV2() {
        List<Map<String, Object>> resultList = new ArrayList<>();

        try (Connection conn = DriverManager.getConnection(url);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName)) {

            // 获取列信息
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            // 遍历结果集并构建Map对象
            while (rs.next()) {
                Map<String, Object> rowMap = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    Object value = rs.getObject(i);

                    // 如果是字符串类型，则去除前后空格
                    if (value instanceof String) {
                        value = ((String) value).trim();
                    }

                    rowMap.put(columnName, value);
                }
                resultList.add(rowMap);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to retrieve data from table " + tableName, e);
        }

        return resultList;
    }

    /**
     * 更新 tlist 表中指定 id 的记录的 bcContent, CycCount 和 status 字段
     */
    public void updateTlistFieldsById(int id, String newContent, int cycCount) {
        // 假设表名为 "tlist" 并且 id 字段为 "devCode"
        String updateSQL = "UPDATE " + tableName + " SET bcContent = ?, bcState = 1, CycCount = ? WHERE devCode = ?";

        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement pstmt = conn.prepareStatement(updateSQL)) {

            // 设置参数值
            pstmt.setString(1, newContent); // bcContent
            pstmt.setInt(2, cycCount);      // CycCount
            pstmt.setInt(3, id);            // devCode

            // 执行更新
            int affectedRows = pstmt.executeUpdate();
            if (affectedRows > 0) {
                System.out.println("Successfully updated record with id = " + id);
            } else {
                System.out.println("No record found with id = " + id);
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("Failed to update data in table " + tableName, e);
        }
    }
    // //
    //    ////
    //    //
}
